﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Moon.DBUtility;
using System.Data;

namespace GPI.Action
{
    public class NewAction
    {
        /// <summary>
        /// 级联查询主频道下第一条新闻
        /// </summary>
        /// <param name="ParentID"></param>
        /// <returns></returns>
        public static object GetModelByNode(string ParentID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  t1.id as id,* from t_newlist t1,t_node t2 where t1.NodeID=t2.id and t2.ParentID=" + ParentID);
            strSql.Append(" order by t1.CreatTime desc");
            return DbHelperOleDb.Query(strSql.ToString()).Tables[0].Rows[0];
        }

        /// <summary>
        /// 查詢新聞列表
        /// </summary>
        /// <param name="ParentID"></param>
        /// <returns></returns>
        public static DataTable GetListByNode(string ParentID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select t1.id as id,Year(t1.CreatTime) as newYear,Format(DatePart(\"m\",t1.CreatTime),\"mmm\") as newMM,DatePart(\"d\",t1.CreatTime) as newDD,* from t_newlist t1,t_node t2 where t1.NodeID=t2.id and t2.ParentID=" + ParentID);
            strSql.Append(" order by t1.CreatTime desc");
            return DbHelperOleDb.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 查詢新聞列表
        /// </summary>
        /// <param name="ParentID"></param>
        /// <returns></returns>
        public static int GetListByCount(string ParentID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_newlist t1,t_node t2 where t1.NodeID=t2.id and t2.ParentID=" + ParentID);
            int count = DbHelperOleDb.GetSingle(strSql.ToString()).ObjectToInt(0);
            int size = count % 4 == 0 ? count / 4 : (count / 4) + 1;
            return size;
        }

        /// <summary>
        /// 查詢新聞列表
        /// </summary>
        /// <param name="ParentID"></param>
        /// <returns></returns>
        public static int GetListByCh(string NodeId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_newlist where NodeID=" + NodeId);
            int count = DbHelperOleDb.GetSingle(strSql.ToString()).ObjectToInt(0);
            int size = count % 4 == 0 ? count / 4 : (count / 4) + 1;
            return size;
        }

        /// <summary>
        /// 级联查询主频道下第一条新闻
        /// </summary>
        /// <param name="ParentID"></param>
        /// <returns></returns>
        public static DataTable GetListByNode(string ParentID, string _CurrentPageIndex, string _PageSize)
        {

            //开始标识
            int starIndex = (_CurrentPageIndex.ToInt(0) - 1) * _PageSize.ToInt(0) <= 0 ? 0 : ((_CurrentPageIndex.ToInt(0) - 1) * _PageSize.ToInt(0));
            // 结束标识
            int endIndex = _CurrentPageIndex.ToInt(0) <= 1 ? 1 * _PageSize.ToInt(0) : (_CurrentPageIndex.ToInt(0) * _PageSize.ToInt(0));

            int count = endIndex - starIndex;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Year(t1.CreatTime) as newYear,Format(DatePart(\"m\",t1.CreatTime),\"mmm\") as newMM,DatePart(\"d\",t1.CreatTime) as newDD,* from t_newlist t1,t_node t2 where  t1.NodeID=t2.id and t2.ParentID=" + ParentID);
            strSql.Append(" order by t1.CreatTime desc");

            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append(" select top " + endIndex + " * from (");
            strSql2.Append(strSql.ToString());
            strSql2.Append(") order by t1.CreatTime desc");
            StringBuilder strSql3 = new StringBuilder();
            strSql3.Append(" select top " + count + " t1.id as id,* from (");
            strSql3.Append(strSql2.ToString());
            strSql3.Append(") order by t1.CreatTime asc");
            return DbHelperOleDb.Query(strSql3.ToString()).Tables[0];
        }

        public static DataTable GetListByCh(string NodeID, string _CurrentPageIndex, string _PageSize)
        {
            //开始标识
            int starIndex = (_CurrentPageIndex.ToInt(0) - 1) * _PageSize.ToInt(0) <= 0 ? 0 : ((_CurrentPageIndex.ToInt(0) - 1) * _PageSize.ToInt(0));
            // 结束标识
            int endIndex = _CurrentPageIndex.ToInt(0) <= 1 ? 1 * _PageSize.ToInt(0) : (_CurrentPageIndex.ToInt(0) * _PageSize.ToInt(0));

            int count = endIndex - starIndex;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Year(t1.CreatTime) as newYear,Format(DatePart(\"m\",t1.CreatTime),\"mmm\") as newMM,DatePart(\"d\",t1.CreatTime) as newDD,* from t_newlist t1 where t1.NodeID=" + NodeID);
            strSql.Append(" order by t1.CreatTime desc");

            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append(" select top " + endIndex + " * from (");
            strSql2.Append(strSql.ToString());
            strSql2.Append(") order by t1.CreatTime desc");
            StringBuilder strSql3 = new StringBuilder();
            strSql3.Append(" select top " + count + " t1.id as id,* from (");
            strSql3.Append(strSql2.ToString());
            strSql3.Append(") order by t1.CreatTime asc");
            return DbHelperOleDb.Query(strSql3.ToString()).Tables[0];
        }
        /// <summary>
        /// 獲取前面幾條數據
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public static DataTable GetTopIndex(string index)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top " + index + " * from t_newlist order by CreatTime desc");
            return DbHelperOleDb.Query(strSql.ToString()).Tables[0];
        }

        public static DataTable GetNewLists(string nodeid, string index)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top " + index + " * from t_newlist ");
            strSql.AppendFormat(" where NodeID in (select ID from t_node where ParentID={0} or ID={0})  ", nodeid);
            strSql.Append(" order by ID desc ");
            return DbHelperOleDb.Query(strSql.ToString()).Tables[0];
        }

        public static DataTable GetSelectTitle(string title, string _CurrentPageIndex, string _PageSize)
        {
            if (!title.IsNullOrEmpty())
            {

                //开始标识
                int starIndex = (_CurrentPageIndex.ToInt(0) - 1) * _PageSize.ToInt(0) <= 0 ? 0 : ((_CurrentPageIndex.ToInt(0) - 1) * _PageSize.ToInt(0));
                // 结束标识
                int endIndex = _CurrentPageIndex.ToInt(0) <= 1 ? 1 * _PageSize.ToInt(0) : (_CurrentPageIndex.ToInt(0) * _PageSize.ToInt(0));

                int count = endIndex - starIndex;
                StringBuilder strSql = new StringBuilder();
                strSql.AppendFormat("select id,NewTitle,description,CreatTime,Year(t1.CreatTime) as newYear,Format(DatePart(\"m\",t1.CreatTime),\"mmm\") as newMM,DatePart(\"d\",t1.CreatTime) as newDD from t_newlist t1 where NewTitle like '%{0}%'", title);
                StringBuilder strSql2 = new StringBuilder();
                strSql2.Append(" select top " + endIndex + " * from (");
                strSql2.Append(strSql.ToString());
                strSql2.Append(") order by CreatTime desc");
                StringBuilder strSql3 = new StringBuilder();
                strSql3.Append(" select top " + count + " * from (");
                strSql3.Append(strSql2.ToString());
                strSql3.Append(") order by CreatTime asc");
                return DbHelperOleDb.Query(strSql3.ToString()).Tables[0];
            }
            else
            {
                return null;
            }
        }
        public static int GetSelectTitleCount(string title, string _size)
        {
            if (!title.IsNullOrEmpty())
            {
                StringBuilder strSql = new StringBuilder();
                strSql.AppendFormat("select count(1) from t_newlist where NewTitle like '%{0}%'", title);
                int count = DbHelperOleDb.GetSingle(strSql.ToString()).ObjectToInt(0);
                int size = count % _size.ObjectToInt(0) == 0 ? count / _size.ObjectToInt(0) : (count / _size.ObjectToInt(0)) + 1;
                return size;
            }
            else
            {
                return 0;
            }
        }
    }
}
